package com.valco.datacontrol.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.valco.data.Book;

public class BookDaoJdbc implements BookDao {
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	private JdbcTemplate jdbcTemplate;

	private RowMapper<Book> userMapper = new RowMapper<Book>() {
		public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
			Book book = new Book();
			book.setIsbn(rs.getString("isbn"));
			book.setIsbn13(rs.getString("isbn13"));
			book.setTitle(rs.getString("title"));
			book.setLink_url(rs.getString("link_url"));
			book.setImage_url(rs.getString("IMAGE_URL"));
			book.setAuthor(rs.getString("author"));
			book.setPrice(rs.getInt("price"));
			book.setDiscount(rs.getInt("discount"));
			book.setPublisher(rs.getString("PUBLISHER"));
			book.setPubdate(rs.getString("pubdate"));
			book.setDescription(rs.getString("description"));
			 
			return book;
		}
	};

	public void add(Book book) {
		try {
			this.jdbcTemplate
			.update("insert into bookstore(isbn,isbn13, title, link_url, image_url, author, price, discount, publisher, pubdate, description)"
					+ "values(?,?,?,?,?,?,?,?,?,?,?)", 
					book.getIsbn(),book.getIsbn13(),
					book.getTitle(), book.getLink_url(),
					book.getImage_url(), book.getAuthor(), book.getPrice(),
					book.getDiscount(), book.getPublisher(),
					book.getPubdate(), book.getDescription());
		} catch (DuplicateKeyException e) {
			// TODO: handle exception
			System.out.println("DuplicateKeyException : "+ e);
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println("Exception : "+ e);
		}
		
	}

	public Book get(String title) {
		return this.jdbcTemplate.queryForObject(
				"select * from bookstore where title = ?",
				new Object[] { title }, this.userMapper);
	}

	public void deleteAll() {
		this.jdbcTemplate.update("delete from bookstore");
	}

	public int getCount() {
		return this.jdbcTemplate.queryForInt("select count(*) from bookstore");
	}

	public List<Book> getAll() {
		return this.jdbcTemplate.query("select * from bookstore order by isbn",
				this.userMapper);
	}

	public void update(Book book) {
		try {
			this.jdbcTemplate
			.update("update bookstore set title = ?, link_url = ?, image_url = ?, author = ?, price = ?, "
					+ "discount = ?, publisher = ?, pubdate = ?, description = ?  where isbn = ? ",
					book.getTitle(), book.getLink_url(),
					book.getImage_url(), book.getAuthor(), book.getPrice(),
					book.getDiscount(), book.getPublisher(),
					book.getPubdate(), book.getDescription(),
					book.getIsbn());
		} catch (DuplicateKeyException e) {
			// TODO: handle exception
			System.out.println("DuplicateKeyException : "+e);
		}
		
	}
}
